package com.hzncc.flowable_diboot.custom_field.service.impl;

import cn.hutool.core.convert.Convert;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.diboot.core.binding.Binder;
import com.diboot.core.service.BaseService;
import com.diboot.core.util.ContextHolder;
import com.diboot.core.util.S;
import com.diboot.core.util.SqlExecutor;
import com.diboot.core.vo.Pagination;
import com.hzncc.flowable_diboot.custom_field.CustomFieldUtil;
import com.hzncc.flowable_diboot.custom_field.entity.CustomTableConfig;
import com.hzncc.flowable_diboot.custom_field.entity.CustomTableItemConfig;
import com.hzncc.flowable_diboot.custom_field.exception.CustomTableException;
import com.hzncc.flowable_diboot.custom_field.service.CustomTableCommonService;
import com.hzncc.flowable_diboot.custom_field.service.CustomTableConfigService;
import com.hzncc.flowable_diboot.custom_field.vo.CustomTableConfigDetailVO;
import com.hzncc.flowable_diboot.custom_field.vo.CustomTableItemConfigListVO;
import com.hzncc.flowable_diboot.custom_field.vo.FilterSettingListVO;
import com.hzncc.flowable_diboot.util.WordUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.log4j.Log4j2;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.*;


@Service
@RequiredArgsConstructor
@Log4j2
public class CustomTableCommonServiceImpl implements CustomTableCommonService {

    private final CustomTableConfigService customTableConfigService;

    private final String sqlCondition = "(\\b(and|AND)\\s+)*\\S+\\s+([=<>]|\\b(like|LIKE|in|IN))\\s+:\\S+";
    private final String sqlCondition2 = "(\\b(and|AND)\\s+)*\\S+\\s+\\b(between|BETWEEN)\\s+:\\S+\\s+\\band\\s+:\\S+";

    /**
     * 根据自定义表格代码执行通用查询操作
     *
     * @param code       表示自定义表格的代码
     * @param param      查询参数，用于条件筛选
     * @param pagination 分页对象，用于控制查询结果的分页
     * @return 返回查询结果列表，具体类型取决于查询的实体或视图实体
     * @throws ClassNotFoundException 如果实体类或视图实体类找不到时抛出此异常
     */
    @Override
    @Transactional(readOnly = true)
    public List<?> commonSelect(String code, Map<String, Object> param, Pagination pagination, boolean isPage) throws ClassNotFoundException {

        // 根据代码获取自定义表格配置
        CustomTableConfig conf = customTableConfigService.getSingleEntity(new QueryWrapper<CustomTableConfig>().lambda().eq(CustomTableConfig::getCode, code));
        // 如果配置不存在，抛出异常
        if (conf == null) {
            throw new CustomTableException(code + "不存在");
        }
        // 将配置转换为详细视图对象
        CustomTableConfigDetailVO confDetail = Binder.convertAndBindRelations(conf, CustomTableConfigDetailVO.class);
        List<CustomTableItemConfigListVO> tableItemConfigs = confDetail.getTableItemConfigs();
        if (tableItemConfigs == null || tableItemConfigs.isEmpty()) {
            throw new CustomTableException(code + "没有配置字段");
        }
        // 检查是否有SQL直接执行
        if (S.isNotBlank(conf.getSql())) {
            return commonSqlSelect(confDetail, param, pagination, isPage);
        }

        // 创建查询包装器并设置实体类
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.setEntityClass(Class.forName(conf.getEntity()));

        // 根据查询参数构建查询条件
        if (param != null && !param.isEmpty()) {
            Map<String, String> conditionMap = confDetail.getConditionMap();
            Set<String> flagFiled = confDetail.flagFiled();
            if (conditionMap != null) {
                for (Map.Entry<String, String> entry : conditionMap.entrySet()) {
                    if (param.containsKey(entry.getKey())) {
                        String actualFiledName = processSqlFieldName(entry.getKey(), flagFiled.contains(entry.getKey()) ? "1" : "0");
                        switch (entry.getValue()) {
                            case "eq" -> queryWrapper.eq(actualFiledName, param.get(entry.getKey()));
                            case "like" -> queryWrapper.like(actualFiledName, param.get(entry.getKey()));
                            case "lt" -> queryWrapper.lt(actualFiledName, param.get(entry.getKey()));
                            case "gt" -> queryWrapper.gt(actualFiledName, param.get(entry.getKey()));
                            case "in" ->
                                    queryWrapper.in(actualFiledName, Arrays.stream(S.split(Optional.ofNullable(param.get(entry.getKey())).orElse("").toString())).toList());
                            //case "range" -> queryWrapper.between(entry.getKey(),param.get(entry.getKey()+"Begin"),param.get(entry.getKey()+"End"));
                            default -> {
                                log.warn(entry.getValue() + "不支持的条件类型");
                            }
                        }
                    }
                }
            }
        }

        // 根据实体类获取对应的BaseService
        BaseService baseService = ContextHolder.getBaseServiceByEntity(queryWrapper.getEntityClass());

        // 根据是否有视图实体，决定返回视图对象列表还是实体列表
        if (S.isNotBlank(conf.getViewEntity())) {
            List voList;
            if (isPage) {
                voList = baseService.getViewObjectList(queryWrapper, pagination, Class.forName(conf.getViewEntity()));
            } else {
                List entityList = baseService.getEntityList(queryWrapper);
                voList = Binder.convertAndBindRelations(entityList, Class.forName(conf.getViewEntity()));
            }
            return voList;
        }
        List entityList;
        if (isPage) {
            entityList = baseService.getEntityList(queryWrapper, pagination);
        } else {
            entityList = baseService.getEntityList(queryWrapper);
        }
        CustomFieldUtil.bindCustomFieldValue(entityList, code);
        return entityList;
    }

    /**
     * 根据自定义表格配置和查询参数处理sql查询条件
     *
     * @param param        查询参数，用于条件筛选
     * @return 返回查询结果列表，具体类型取决于查询的实体或视图实体
     * @throws ClassNotFoundException 如果实体类或视图实体类找不到时抛出此异常
     */
    private String processCondition(String sql, List<FilterSettingListVO> filterSettings, Map<String, Object> param) {
        if (filterSettings == null) {
            return sql;
        }
        for (FilterSettingListVO filterSetting : filterSettings) {
            if (param.containsKey(filterSetting.getFieldName())) {
                if (S.equals("in", filterSetting.getCondition())) {
                    Object val = param.get(filterSetting.getFieldName());
                    List<String> arr = Arrays.stream(S.split(Optional.ofNullable(val).orElse("").toString())).toList();
                    sql = sql.replace(":" + filterSetting.getFieldName(), "(" + String.join(",", Collections.nCopies(arr.size(), "?")) + ")");
//                    sql = sql.replace(":" + filterSetting.getFieldName(), "(?)");
                } else {
                    sql = sql.replace(":" + filterSetting.getFieldName(), "?");
                }
            }
        }
        // 处理没有传入参数的查询条件
        sql = sql.replaceAll(sqlCondition, "").replaceAll(sqlCondition2, "");
        return sql.replaceAll("\\b(where|WHERE)\\s+and\\s+", "where ");
    }

    /**
     * 执行自定义sql
     *
     * @param confDetail 自定义表格配置详情对象，包含sql语句和过滤设置等信息
     * @param param      传递给sql语句的参数，用于替换sql中的占位符
     * @param pagination 分页对象，包含当前页码和每页大小等信息
     * @param isPage
     * @return 查询结果列表，具体类型取决于查询的数据
     */
    private List<?> commonSqlSelect(CustomTableConfigDetailVO confDetail, Map<String, Object> param, Pagination pagination, boolean isPage) {
        // 获取原始sql语句
        String sql = confDetail.getSql();
        // 获取过滤设置列表
        List<FilterSettingListVO> filterSettings = confDetail.getFilterSettings();
        // 如果过滤设置和参数都不为空，则遍历过滤设置，构建动态where子句
        List<Object> params = new ArrayList<>();
        if (filterSettings != null && param != null) {
            for (FilterSettingListVO e : filterSettings) {
                // 如果参数中包含过滤字段，则将其按顺序添加到查询参数
                if (param.containsKey(e.getFieldName())) {
                    Object val = param.get(e.getFieldName());
                    if (e.getConditionChar().equals("like")) {
                        params.add("%" + val + "%");
                    } else if (e.getConditionChar().equals("in")) {
                        params.add(Arrays.stream(S.split(Optional.ofNullable(val).orElse("").toString())).toList());
                    } else {
                        params.add(val);
                    }
                }
            }
        }
        // 使用构建的where子句替换sql中的占位符
        String finalSql = processCondition(sql, filterSettings, param);
        // 构建用于计算总记录数的sql语句
        String countSql = "SELECT count(*) as total from (" + finalSql + ") as t";
        if (isPage) {
            // todo 数据库兼容
            // 构建分页所需的limit子句
            String limit = "limit " + pagination.getPageSize() * (pagination.getPageIndex() - 1) + "," + pagination.getPageSize();
            // 将limit子句添加到sql语句末尾
            finalSql = finalSql + " " + limit;
        }
        // 记录执行的sql语句
        log.info("执行sql：{}", finalSql);
        // 初始化查询结果列表
        List<Map<String, Object>> maps = new ArrayList<>();
        Connection connection = null;
        try {
            List<Map<String, Object>> countMap = new ArrayList<>();
            // 验证sql语句的合法性
            //String checkSql = finalSql.replaceAll("\\?", "''");
            //if (SqlExecutor.validateQuery(checkSql)) {
            connection = SqlExecutor.getJdbcTemplate().getDataSource().getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(finalSql);
            int paramIndex = 1;
            for (Object p : params) {
                if (p instanceof List arr) {
                    for (Object val : arr) {
                        preparedStatement.setObject(paramIndex++, val);
                    }
                } else {
                    preparedStatement.setObject(paramIndex++, p);
                }
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            List<String> fieldNames = confDetail.getTableItemConfigs().stream().map(CustomTableItemConfigListVO::getFieldName).toList();
            while (resultSet.next()) {
                Map<String, Object> row = new HashMap<>();
                fieldNames.forEach(fieldName -> {
                    try {
                        row.put(fieldName, resultSet.getObject(fieldName));
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                });
                maps.add(row);
            }
            PreparedStatement preparedCount = connection.prepareStatement(countSql);
            paramIndex = 1;
            for (Object p : params) {
                if (p instanceof List arr) {
                    for (Object val : arr) {
                        preparedCount.setObject(paramIndex++, val);
                    }
                } else {
                    preparedCount.setObject(paramIndex++, p);
                }
            }
            ResultSet countResult = preparedCount.executeQuery();
            while (countResult.next()) {
                Map<String, Object> countRow = new HashMap<>();
                countRow.put("total", countResult.getObject("total"));
                countMap.add(countRow);
            }
            connection.close();
            preparedStatement.close();
            preparedCount.close();
            resultSet.close();
            countResult.close();
            if (countMap.isEmpty()) {
                countMap.add(Map.of("total", 0));
            }
            pagination.setTotalCount(Long.parseLong(countMap.get(0).get("total").toString()));
            //} else {
            //    // 如果sql语句不合法，记录错误并抛出异常
            //    log.error("sql语句不合法:{}", finalSql);
            //    throw new RuntimeException("sql语句不合法");
            //}
        } catch (Exception e) {
            // 捕获异常并抛出运行时异常
            throw new RuntimeException(e);
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    // 处理关闭连接时的异常
                    e.printStackTrace();
                }
            }
        }
        // 返回查询结果列表
        return maps;
    }

    /**
     * 处理sql字段名称
     *
     * @param fieldName 字段名称
     * @param rangeFlag 范围标志
     * @return 处理后的字段名称
     */
    private String processSqlFieldName(String fieldName, String rangeFlag) {
        if (S.equals("1", rangeFlag)) {
            if (fieldName.endsWith("Begin")) {
                return unCamel(fieldName.substring(0, fieldName.length() - 5));
            } else {
                return unCamel(fieldName.substring(0, fieldName.length() - 3));
            }
        } else {
            return unCamel(fieldName);
        }
    }

    /**
     * 驼峰转下划线
     *
     * @param str 驼峰字符串
     * @return 下划线字符串
     */
    private String unCamel(String str) {
        if (S.isBlank(str)) {
            return "";
        }
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < str.length(); i++) {
            char c = str.charAt(i);
            if (Character.isUpperCase(c)) {
                sb.append("_");
            }
            sb.append(Character.toLowerCase(c));
        }
        return sb.toString();
    }

    @Override
    public File exportExcel(Map<String, Object> param, String code, Pagination pagination, Boolean pageExport) throws ClassNotFoundException {
        // 获取数据
        List<?> data = this.commonSelect(code, param, pagination, pageExport);
        // 获取配置
        CustomTableConfigDetailVO userConfig = customTableConfigService.getConfig(code);
        // 创建工作簿
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet(S.isNotBlank(userConfig.getName()) ? userConfig.getName() : "sheet1");

            // 获取导出字段
            List<CustomTableItemConfigListVO> tableItemConfigs = userConfig.getTableItemConfigs().stream()
                    .filter(CustomTableItemConfig::getExportable).sorted(Comparator.comparingInt(CustomTableItemConfig::getSort)).toList();
            // 创建表头
            Row headerRow = sheet.createRow(0);
            CellStyle headerStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true); // 设置字体加粗
            font.setFontHeightInPoints((short) 14); // 设置字体大小
            headerStyle.setFont(font);
            headerStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); // 设置背景颜色
            headerStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中对齐
            headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中对齐
            // 获取表头
            List<String> headers = tableItemConfigs.stream().map(CustomTableItemConfig::getFieldLabel).toList();
            for (int i = 0; i < headers.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers.get(i));
                sheet.setColumnWidth(i, headers.get(i).length() * 4 * 256);
                cell.setCellStyle(headerStyle);
            }

            // 填充数据
            int rowNum = 1;
            // 获取字段名
            List<String> fieldNames = tableItemConfigs.stream().map(CustomTableItemConfig::getFieldName).toList();
            for (Object datum : data) {
                Row row = sheet.createRow(rowNum++);
                for (int i = 0; i < fieldNames.size(); i++) {
                    Object fieldValue = CustomFieldUtil.getFieldValue(datum, fieldNames.get(i));
                    row.createCell(i).setCellValue(Convert.convert(String.class, fieldValue));
                }
            }
            String templateFilePath = WordUtils.getTemplateFilePath();
            File file = new File(templateFilePath + "/" + userConfig.getName() + LocalDate.now() + ".xlsx");
            if (file.exists()) {
                file.delete();
            }
            // 将工作簿写入文件
            try (FileOutputStream fileOut = new FileOutputStream(file)) {
                workbook.write(fileOut);
            } catch (IOException e) {
                throw new CustomTableException(e);
            }
            return file;
        } catch (Exception e) {
            throw new CustomTableException(e);
        }
    }
}
